/*
 Navicat Premium Data Transfer

 Source Server         : 47.105.61.251
 Source Server Type    : MySQL
 Source Server Version : 80028
 Source Host           : 47.105.61.251:3306
 Source Schema         : ede-signup

 Target Server Type    : MySQL
 Target Server Version : 80028
 File Encoding         : 65001

 Date: 29/05/2023 20:14:18
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sys_api_sql
-- ----------------------------
DROP TABLE IF EXISTS `sys_api_sql`;
CREATE TABLE `sys_api_sql` (
  `model` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '模块',
  `code` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '编码',
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '接口名称',
  `sqlcn` varchar(4000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '代码内容',
  `id` int NOT NULL AUTO_INCREMENT,
  `childs` varchar(255) NOT NULL COMMENT '子类',
  `leftinfo` varchar(255) DEFAULT NULL COMMENT '左关联',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of sys_api_sql
-- ----------------------------
BEGIN;
INSERT INTO `sys_api_sql` VALUES ('match', 'match', '赛事列表', 'select \nt.id,\nt.match_name \n,t.season\n,DATE_FORMAT(t.begin_date,\'%Y-%m-%d %H:%i:%s\') begindate \n,concat(DATE_FORMAT(t.begin_signup,\'%m月%d日\') ,\'~\',DATE_FORMAT(t.end_signup,\'%m月%d日\')) signdate \n from sp_match t  where 1=1 and t.`status`=1 if:id[ and t.id =#{p.id} ]\nand (\n    \n\nt.match_level  = 1 or #{p.user.province} = t.province\n)\nand (\n\nt.match_level  <=2  or t.is_open = 1 or #{p.user.city} = t.city or t.city = \'-1\'\n)\nand t.begin_signup < NOW()', 4, '', NULL);
INSERT INTO `sys_api_sql` VALUES ('match', 'matchRecord', '报名记录', '##哈哈手的画法\nselect t.match_name \n,sj.dict_label seasonname\n,DATE_FORMAT(t.begin_date,\'%Y-%m-%d %H:%i:%s\') begindate \n,concat(DATE_FORMAT(t.begin_signup,\'%m月%d日\') ,\'~\',DATE_FORMAT(t.end_signup,\'%m月%d日\')) signdate  , t.id ,t.id detail\n from sp_match t \nleft join sys_dict_data sj on sj.id = t.season\n\n where t.id in (\n \n select  mi.match_id from sp_match_item_team t2 \ninner join sp_match_item  mi on mi.id = t2.match_item_id\ninner join sp_match m on m.id = mi.match_id\ninner join sp_match_item_team_user muser on muser.match_item_team_id = t2.id\nand (#{p.user.roleId} !=4 or  muser.user_id = #{p.user.id} )\nif:userId[ \n\nand muser.user_id = #{p.userId}\n]\nwhere t2.team_id = #{p.user.teamId}\n if:matchStatus[ \n\nand t2.status = #{p.matchStatus}\n]\n ) \n\nif:endDate[and t.end_date < #{ p.endDate}]\nif:beginDate[ and t.begin_date > #{ p.beginDate}]\n if:season[ and t.season=#{p.season}]\n if:matchLevel[and t.match_level =#{p.matchLevel} ]\n if:province[and t.province =#{p.province}]\n if:city[and t.city =#{p.city}]\n if:searchStr[and ( INSTR(t.match_name,#{p.searchStr})>1 )]', 5, 'matchRecordItem:detail:detail:userId', NULL);
INSERT INTO `sys_api_sql` VALUES ('match', 'matchRecordItem', '报名记录明细', 'select\nt.id\n,t.rise_file\n,DATE_FORMAT(t.create_date,\'%Y-%m-%d %H:%i:%s\') create_date\n ,mi.match_id\n,t.team_id\n,mclass.dict_label classname\n,mtype.dict_label typename ,\n t.team_name ,bi.item_name ,g.dict_label group_name , t.sort , bi.is_draw\n,(\nselect GROUP_CONCAT(csu.real_name SEPARATOR \'、\') from  sp_match_item_team cmi \ninner join sp_match_item_team_user cmu on cmu.match_item_team_id = cmi.id\ninner join sp_user csu on csu.id = cmu.user_id \nwhere cmu.type=1 and cmi.id=t.id \n\n)\n\n\nreal_name ,t.`status`\nif:myinfo[\n\n,(\nselect GROUP_CONCAT(csu.real_name SEPARATOR \'、\') from  sp_match_item_team cmi \ninner join sp_match_item_team_user cmu on cmu.match_item_team_id = cmi.id\ninner join sp_user csu on csu.id = cmu.user_id \nwhere cmu.type=2 and cmi.id=t.id\n) teachers\n\n]\n \n\n\n\n\nfrom sp_match_item_team t\n INNER JOIN sp_match_item mi on mi.id = t.match_item_id\nleft join sp_base_match_item bi on bi.id = mi.base_match_item_id\nleft join sys_dict_data mclass on mclass.id = bi.item_classify\nleft join sys_dict_data mtype on mtype.id = bi.item_type\nleft join sys_dict_data g on t.group_id = g.id\nif:userId[\ninner join sp_match_item_team_user smuser on  smuser.match_item_team_id =t.id\nand smuser.user_Id =#{p.userId}  and smuser.type=1\n]\n\nwhere 1=1 if:detail[ and  mi.match_id = #{p.detail}]\nand t.team_id =#{p.user.teamId}\nif:id[ and  t.id = #{p.id}]\n', 6, '', NULL);
INSERT INTO `sys_api_sql` VALUES ('user', 'userList', '用户列表', 'select t.id ,t.real_name,t.company,  DATE_FORMAT(t.create_date,\'%Y-%m-%d %H:%i:%s\')  create_date ,t2.id teamUserId\nfrom sp_user t \nleft join sp_team_user t2 on t.id = t2.user_id\nwhere 1=1   \nand t2.team_id =#{p.user.teamId}\nand ( #{p.user.roleId}  !=4 or  t.id = #{p.user.id} )\n if:type[ and t2.type=#{p.type}  ] \n\nif:season[ \nand  (select count(1) from sp_match_item_team_user r \ninner join sp_match_item_team rm on rm.id = r.match_item_team_id\ninner join sp_match_item sumi on sumi.id = rm.match_item_id\ninner join sp_match usm on usm.id = sumi.match_id\nwhere \n r.user_id =t.id and usm.season =#{p.season}\n\nand rm.status not in (3,7,8)\n)<2\n]\n\nif:noItemId[\nand not exists ( \nselect si.* from sp_match_item_team_user r \ninner  join sp_match_item_team  si on si.id = r.match_item_team_id \nwhere  si.match_item_id  =\n#{p.noItemId} and r.user_id =  t.id  and r.status!=0 \n\nand si.status not in (3,7,8)\n)\n\n]', 7, '', NULL);
INSERT INTO `sys_api_sql` VALUES ('match', 'matchInfo', '赛事详情', 'select t.match_name \n,DATE_FORMAT(t.begin_date,\'%Y-%m-%d %H:%i:%s\') begindate \n,concat(DATE_FORMAT(t.begin_signup,\'%m月%d日\') ,\'~\',DATE_FORMAT(t.end_signup,\'%m月%d日\')) signdate\n,t.id detail \n from sp_match t \nwhere t.id =#{p.id}', 8, 'matchItem:detail:matchid', NULL);
INSERT INTO `sys_api_sql` VALUES ('match', 'matchItem', '赛事明细', 'select t.id\n,t.match_id\n,t.base_match_item_id\n,bi.item_classify\n,bi.item_type\n\n,mclass.dict_label classname\n,mtype.dict_label typename \n,bi.item_name \n,t.teacher_num\n,t.teacher_num_max\n,t.student_num\n,t.student_num_max \nfrom sp_match_item t \nleft join sp_base_match_item bi on bi.id = t.base_match_item_id\nleft join sys_dict_data mclass on mclass.id = bi.item_classify\nleft join sys_dict_data mtype on mtype.id = bi.item_type\nwhere 1=1  if:matchid[ and t.match_id = #{p.matchid} ]\n if:id[  and t.id = #{p.id} ]\n\n\n', 9, '', NULL);
INSERT INTO `sys_api_sql` VALUES ('dict', 'dictList', '字典', 'select t.id code ,t.dict_label name from sys_dict_data t \nwhere t.dict_type_id =#{p.pid}', 10, '', NULL);
INSERT INTO `sys_api_sql` VALUES ('user', 'team', '代表队', 'select su.real_name,t.team_name ,t.id from sp_team t \nleft join sp_team_user stu on stu.team_id = t.id and stu.type =1\nleft join sp_user su on su.id = stu.user_id\nwhere  1=1\nif:id[ and t.id =#{p.id}]', 12, '', NULL);
INSERT INTO `sys_api_sql` VALUES ('sys', 'dictList', '字典', 'select t.id  code ,t.dict_label name ,t.dict_value  value  from  sys_dict_data t \nwhere 1=1 \nand t.dict_type_id=#{p.pid}\norder by t.sort;', 13, '', NULL);
INSERT INTO `sys_api_sql` VALUES ('sys', 'areaDict', '地区接口', 'select t.code,t.name  from  t_area t where t.pcode=#{p.pid}', 14, '', NULL);
INSERT INTO `sys_api_sql` VALUES ('match', 'signRecord', '后台报名记录', 'SELECT\n	t.id,\n	t.team_id,\n	tm.team_name,\n	bi.item_name,\n	t.team_name,\n	group_concat(u.real_name  separator \'、\') real_name\nFROM\n	sp_match_item_team t,\n	sp_match_item_team_user tu,\n	sp_user u,\n  sp_team tm,\n	sp_base_match_item bi\nWHERE\n	tu.match_item_team_id = t.id\n	and tu.user_id = u.id\n	and t.team_id = tm.id\n	and t.match_item_id = bi.id\n	and tu.type =1 \n	group by \n	t.id,\n	t.team_id,\n	tm.team_name,\n	bi.item_name,\n	t.team_name', 15, ' ', NULL);
INSERT INTO `sys_api_sql` VALUES ('user', 'userInfo', '用户详情', 'select  t.id ,t.real_name ,t.sex ,t.company ,t.header \nif:matchId[\n,st.ticket \n]\nfrom sp_user t \nif:matchId[\nleft join sp_ticket st on st.user_id = t.id and st.match_id=#{p.matchId}\n]\nwhere t.id =#{p.id}', 16, '', NULL);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
